package com.salary.utils;

import com.salary.model.SalesRecord;
import com.salary.model.SalaryResult;
import com.salary.model.SalesInfo;
import com.salary.utils.reader.DataReader;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel处理工具类
 */
public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);




    /**
     * 计算工资
     *
     * @param records 工资记录列表
     * @param salesInfo 工资资料信息
     * @return 计算后的工资结果列表
     */
    public static List<SalaryResult> calculateSalary(List<SalesRecord> records, SalesInfo salesInfo) {
        if (records == null || records.isEmpty()) {
            return new ArrayList<>();
        }

        // 计算提成单价和行提成 - 根据不同业务员使用不同规则
        for (SalesRecord record : records) {
            // 针对不同业务员使用不同的提成计算规则
            calculateCommissionRate(record);
        }

        // 计算总销量（用于计算特殊人员的提成）
        double totalQuantity = records.stream().mapToDouble(SalesRecord::getQuantity).sum();
        
        // 获取全局销售总量（不包含东莞诚昌的销量）
        double totalSalesQuantityExcludeSpecial = 0.0;
        SalesInfo globalSalesInfo = GlobalDataStore.getSalaryInfo();
        if (globalSalesInfo != null) {
            totalSalesQuantityExcludeSpecial = globalSalesInfo.getTotalSalesQuantity();
            logger.info("获取到全局销售总量（不含东莞诚昌）: " + totalSalesQuantityExcludeSpecial + "吨");
        } else {
            totalSalesQuantityExcludeSpecial = totalQuantity;
            logger.warn("未找到全局销售总量数据，使用当前记录总和: " + totalQuantity + "吨");
        }

        // 按业务员分组，计算正常业务员的提成
        Map<String, Double> salespersonCommission = records.stream()
                .filter(r -> !"刘涛".equals(r.getSalesperson()) && !"卢东韬".equals(r.getSalesperson()))
                .collect(Collectors.groupingBy(
                        SalesRecord::getSalesperson,
                        Collectors.summingDouble(SalesRecord::getCommission)
                ));

        // 添加特殊业务员，使用原始总销量计算
        // 刘涛（营销经理）的提成 - 按总销量*4计算（使用原始总销量）
        salespersonCommission.put("刘涛", totalQuantity * 4);
        logger.info("刘涛（营销经理）的提成 = 原始总销量(" + totalQuantity + "吨) × 4 = " + 
                  (totalQuantity * 4) + "元");

        // 卢东韬（副总）的提成计算已修改，不再使用总销量乘以1.65的方式
        // 奖金预留固定为-1000元，政策奖惩固定为10000元加上原值，总奖金为系数表对应系数×总销量×考核分×0.01
        // 卢东韬的提成单独在writeSalaryResultsToTemplate方法中特殊处理
        logger.info("卢东韬（副总）的提成计算规则已更新，将在写入模板时特殊处理");

        // 转换为SalaryResult列表，并处理收承兑扣款
        List<SalaryResult> results = new ArrayList<>();
        int id = 1;
        for (Map.Entry<String, Double> entry : salespersonCommission.entrySet()) {
            String salesperson = entry.getKey();
            double commission = entry.getValue();

            // 初始化各项调整金额
            double acceptanceDeduction = 0.0;     // 收承兑扣款
            double overdueDeduction = 0.0;        // 超期回款扣款
            double accountStatementDeduction = 0.0; // 业务未按时交回对账单扣款
            double marketingReward = 0.0;         // 营销奖励
            double marketingPenalty = 0.0;        // 营销惩罚
            double refundableAmount = 0.0;        // 申请补回罚款
            double policyRewardPenalty = 0.0;   // 政策奖惩

            // 只有在提供了salaryInfo的情况下才进行各项调整
            if (salesInfo != null) {
                // 收承兑扣款 - 刘涛不扣
                if (!"刘涛".equals(salesperson)) {
                    acceptanceDeduction = salesInfo.calculateSalespersonDeduction(salesperson);
                    logger.info("业务员 [" + salesperson + "] 收承兑扣款计算: " +
                              "吨数=" + salesInfo.getSalespersonAcceptance().getOrDefault(salesperson, 0.0) + ", " +
                              "扣款金额=" + acceptanceDeduction);
                } else {
                    logger.info("业务员 [刘涛] 为营销经理，不扣收承兑款");
                }

                // 超期回款扣款
                overdueDeduction = salesInfo.getOverduePaymentDeduction(salesperson);
                if (overdueDeduction != 0) {
                    logger.info("业务员 [" + salesperson + "] 超期回款扣款: " + overdueDeduction);
                }

                // 业务未按时交回对账单扣款
                accountStatementDeduction = salesInfo.getAccountStatementDeduction(salesperson);
                if (accountStatementDeduction != 0) {
                    logger.info("业务员 [" + salesperson + "] 未按时交回对账单扣款: " + accountStatementDeduction);
                }

                // 营销奖励
                marketingReward = salesInfo.getMarketingReward(salesperson);
                if (marketingReward != 0) {
                    logger.info("业务员 [" + salesperson + "] 营销奖励: " + marketingReward);
                }

                // 营销惩罚
                marketingPenalty = salesInfo.getMarketingPenalty(salesperson);
                if (marketingPenalty != 0) {
                    logger.info("业务员 [" + salesperson + "] 营销惩罚: " + marketingPenalty);
                }

                // 计算政策奖惩
                policyRewardPenalty = marketingReward - marketingPenalty;
                if (policyRewardPenalty != 0) {
                    logger.info("业务员 [" + salesperson + "] 政策奖惩 (营销奖励 - 营销罚款): " + policyRewardPenalty);
                }

                // 申请补回罚款
                refundableAmount = salesInfo.getRefundableDeduction(salesperson);
                if (refundableAmount != 0) {
                    logger.info("业务员 [" + salesperson + "] 申请补回罚款: " + refundableAmount);
                }
            }

            // 计算最终提成 = 原提成 - 各种扣款 + 申请补回罚款 (营销奖励和惩罚不再计入总提成)
            double finalCommission = commission
                    - acceptanceDeduction       // 减去收承兑扣款
                    - overdueDeduction          // 减去超期回款扣款
                    - accountStatementDeduction // 减去未按时交回对账单扣款
                    // + marketingReward           // 营销奖励不再计入总提成
                    // - marketingPenalty          // 营销惩罚不再计入总提成
                    + refundableAmount;         // 加上申请补回罚款

            // 保留两位小数
            finalCommission = Math.round(finalCommission * 100) / 100.0;

            results.add(new SalaryResult(id++, salesperson, finalCommission, policyRewardPenalty));
            logger.info("业务员: " + salesperson +
                       ", 原提成: " + commission +
                       ", 收承兑扣款: " + acceptanceDeduction +
                       ", 超期回款扣款: " + overdueDeduction +
                       ", 未按时交回对账单扣款: " + accountStatementDeduction +
                       ", 营销奖励: " + marketingReward +
                       ", 营销惩罚: " + marketingPenalty +
                       ", 政策奖惩: " + policyRewardPenalty +
                       ", 申请补回罚款: " + refundableAmount +
                       ", 最终提成: " + finalCommission);
        }

        // 按序号排序
        results.sort(Comparator.comparingInt(SalaryResult::getId));

        return results;
    }

    /**
     * 计算单条记录的提成单价和行提成
     *
     * @param record 工资记录
     */
    public static void calculateCommissionRate(SalesRecord record) {
        double commissionRate = 0;
        String salesperson = record.getSalesperson();
        String stockName = record.getStockName();
        String customerCode = record.getCustomerCode();
        String warehouse = record.getWarehouse();
        double paperProfit = record.getPaperProfit();

        // 1. 根据存货名称判断基础提成单价
        if (stockName != null) {
            if (stockName.contains("幸运鸟") || stockName.contains("新东昇")) {
                commissionRate = 40;
                // 如果客户编码以D2开头，提成单价折半
                if (customerCode != null && customerCode.startsWith("D2")) {
                    commissionRate = 20;
                }
            } else if (stockName.contains("白鸥")) {
                commissionRate = 50;
                // 如果客户编码以D2开头，提成单价折半
                if (customerCode != null && customerCode.startsWith("D2")) {
                    commissionRate = 25;
                }
            } else {
                // 非特殊存货名称
                if (paperProfit < 150 || (customerCode != null && customerCode.startsWith("D2"))) {
                    commissionRate = 12.5;
                } else {
                    commissionRate = 25;
                }
            }
        }

        // 2. 如果是刘平或该条记录的业务员2包含"公司资源"，提成单价乘以0.6
        if ("刘平".equals(salesperson) ||
            (record.getSalesperson2() != null && record.getSalesperson2().contains("公司资源"))) {
            double originalRate = commissionRate;
            commissionRate *= 0.6;
            logger.info("业务员 [" + salesperson + "] 的此条记录应用特殊规则(60%)，原提成单价: " +
                      originalRate + ", 调整后: " + commissionRate);
        }

        // 3. 如果仓库包含"直送仓"，提成单价加2.5元（刘涛除外）
        if (warehouse != null && warehouse.contains("直送仓") && !"刘涛".equals(salesperson)) {
            commissionRate += 2.5;
            logger.info("业务员 [" + salesperson + "] 使用直送仓，提成单价增加2.5元到: " + commissionRate);
        } else if (warehouse != null && warehouse.contains("直送仓") && "刘涛".equals(salesperson)) {
            logger.info("业务员 [刘涛] 为营销经理，使用直送仓不增加提成单价");
        }

        // 设置提成单价
        record.setCommissionRate(commissionRate);

        // 4. 计算行提成 = 数量 × 最终提成单价
        double commission = record.getQuantity() * commissionRate;
        record.setCommission(commission);
    }




    /**
     * 计算工龄津贴，每满一年增加50元，最高500元
     *
     * @param entryDateStr 入职日期字符串，格式为"yyyy-MM-dd"或"yyyy/MM/dd"或Java Date默认格式
     * @return 工龄津贴金额
     */
    public static double calculateServiceAllowance(String entryDateStr) {
        if (entryDateStr == null || entryDateStr.trim().isEmpty()) {
            return 0.0;
        }

        try {
            // 统一日期格式，将yyyy/MM/dd转换为yyyy-MM-dd
            entryDateStr = entryDateStr.trim().replace("/", "-");

            // 解析日期格式
            LocalDate entryDate = null;

            // 首先检查是否是Java的Date默认格式，如 "Sun Sep 26 00:00:00 CST 2021"
            if (entryDateStr.matches("\\w{3} \\w{3} \\d{2} \\d{2}:\\d{2}:\\d{2} \\w{3} \\d{4}")) {
                logger.info("检测到Java Date默认格式: " + entryDateStr);

                // 提取年月日信息
                String[] parts = entryDateStr.split(" ");
                if (parts.length >= 6) {
                    // 获取月份
                    String month = parts[1];
                    int monthValue = switch (month.toLowerCase()) {
                        case "jan" -> 1;
                        case "feb" -> 2;
                        case "mar" -> 3;
                        case "apr" -> 4;
                        case "may" -> 5;
                        case "jun" -> 6;
                        case "jul" -> 7;
                        case "aug" -> 8;
                        case "sep" -> 9;
                        case "oct" -> 10;
                        case "nov" -> 11;
                        case "dec" -> 12;
                        default -> 0;
                    };

                    // 获取日
                    int day = Integer.parseInt(parts[2]);

                    // 获取年
                    int year = Integer.parseInt(parts[5]);

                    // 构建LocalDate
                    entryDate = LocalDate.of(year, monthValue, day);
                    logger.info("成功从Date格式解析日期: " + entryDate);
                }
            }

            // 如果上述方法未成功解析，尝试其他格式
            if (entryDate == null) {
                if (entryDateStr.matches("\\d{4}-\\d{1,2}-\\d{1,2}")) {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                    entryDate = LocalDate.parse(entryDateStr, formatter);
                } else if (entryDateStr.matches("\\d{4}\\.\\d{1,2}\\.\\d{1,2}")) {
                    entryDate = LocalDate.parse(entryDateStr.replace(".", "-"), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    } else {
                    // 尝试按照 yyyy-MM-dd HH:mm:ss 格式解析（如果有时间部分）
                    try {
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        LocalDateTime dateTime = LocalDateTime.parse(entryDateStr, formatter);
                        entryDate = dateTime.toLocalDate();
                    } catch (DateTimeParseException e) {
                        logger.warn("无法解析入职日期: " + entryDateStr + "，将尝试更多格式");

                        // 尝试更多格式
                        try {
                            // 尝试格式 dd/MM/yyyy
                            entryDate = LocalDate.parse(entryDateStr.replace("-", "/"), DateTimeFormatter.ofPattern("dd/MM/yyyy"));
                        } catch (DateTimeParseException e2) {
                            try {
                                // 尝试格式 MM/dd/yyyy
                                entryDate = LocalDate.parse(entryDateStr.replace("-", "/"), DateTimeFormatter.ofPattern("MM/dd/yyyy"));
                            } catch (DateTimeParseException e3) {
                                // 最后一次尝试：如果包含年份信息，尝试直接提取年份并估算工作年限
                                if (entryDateStr.matches(".*\\d{4}.*")) {
                                    // 尝试找出4位数字作为年份
                                    java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("\\d{4}");
                                    java.util.regex.Matcher matcher = pattern.matcher(entryDateStr);
                                    if (matcher.find()) {
                                        int year = Integer.parseInt(matcher.group());
                                        // 使用当前年份减去入职年份，得到大致的工作年限
                                        int currentYear = LocalDate.now().getYear();
                                        int yearsWorked = currentYear - year;
                                        logger.info("无法完全解析日期，但找到年份: " + year + "，估算工作年限: " + yearsWorked + "年");

                                        // 计算工龄津贴（每年50元，最高500元）
                                        double allowance = Math.min(yearsWorked * 50, 500);
                                        logger.info("估算工龄津贴: " + allowance + "元");
                                        return allowance;
                                    }
                                }

                                logger.error("所有格式都无法解析入职日期: " + entryDateStr);
                                return 0.0;
                            }
                        }
                    }
                }
            }

            // 获取当前日期
            LocalDate currentDate = LocalDate.now();

            // 计算工作年限（取整）
            long years = ChronoUnit.YEARS.between(entryDate, currentDate);
            logger.info("入职日期: " + entryDate + "，当前日期: " + currentDate + "，工作年限: " + years + "年");

            // 计算工龄津贴（每年50元，最高500元）
            double allowance = Math.min(years * 50, 500);
            logger.info("工龄津贴: " + allowance + "元");

            return allowance;

        } catch (Exception e) {
            logger.error("计算工龄津贴时发生错误: " + e.getMessage(), e);
            return 0.0;
        }
    }

    /**
     * 处理加工组数据，计算工资并写入输出表格
     *
     * @param processingFilePath 加工组Excel文件路径
     * @param sheet 输出表格工作表
     * @param nameColIndex 姓名列索引
     * @param totalBonusColIndex 总奖金列索引
     * @return 是否成功处理数据
     */
    public static boolean processProcessingGroupData(String processingFilePath, Sheet sheet, int nameColIndex, int totalBonusColIndex) {
        if (processingFilePath == null || processingFilePath.isEmpty()) {
            logger.error("加工组文件路径为空");
            return false;
        }

        try (FileInputStream fis = new FileInputStream(processingFilePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 查找"分切组"工作表
            Sheet cuttingSheet = null;

            // 输出所有工作表名称，帮助调试
            logger.info("加工组Excel文件中包含的工作表：");
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                String sheetName = workbook.getSheetName(i);
                logger.info(" - " + sheetName);

                if (sheetName.contains("分切组")) {
                    cuttingSheet = workbook.getSheetAt(i);
                    logger.info("找到分切组工作表: " + sheetName);
                }
            }

            if (cuttingSheet == null) {
                logger.error("未找到分切组工作表");
                return false;
            }

            // 查找表头
            int nameCol = -1, positionCol = -1, quantityCol = -1, partTimeCol = -1, wasteCol = -1;
            int headerRowIndex = -1;

            // 查找表头行
            for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                Row row = cuttingSheet.getRow(rowIndex);
                if (row == null) continue;

                // 重置列索引
                nameCol = -1;
                positionCol = -1;
                quantityCol = -1;
                partTimeCol = -1;
                wasteCol = -1;

                // 输出当前行的所有单元格值，帮助调试
                StringBuilder rowContent = new StringBuilder("第" + (rowIndex+1) + "行内容: ");
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        String value = DataReader.getCellValueAsString(cell).trim();
                        rowContent.append("[").append(value).append("] ");
                    }
                }
                logger.info(rowContent.toString());

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) continue;

                    String value = DataReader.getCellValueAsString(cell).trim();
                    if (value.contains("名称")) {
                        nameCol = i;
                        logger.info("找到名称列，索引: " + i);
                    }
                    else if (value.contains("岗位")) {
                        positionCol = i;
                        logger.info("找到岗位列，索引: " + i);
                    }
                    else if (value.contains("数量") && !value.contains("底板") && !value.contains("盖板")) {
                        quantityCol = i;
                        logger.info("找到数量列，索引: " + i);
                    }
                    else if (value.contains("兼职量")) {
                        partTimeCol = i;
                        logger.info("找到兼职量列，索引: " + i);
                    }
                    else if (value.contains("损耗量")) {
                        wasteCol = i;
                        logger.info("找到损耗量列，索引: " + i);
                    }
                }

                if (nameCol != -1 && positionCol != -1 && quantityCol != -1 && partTimeCol != -1 && wasteCol != -1) {
                    headerRowIndex = rowIndex;
                    logger.info("找到分切组表头，在第" + (rowIndex+1) + "行");
                    break;
                }
            }

            if (headerRowIndex == -1) {
                logger.error("未找到分切组表头行");
                logger.error("缺少的列: " +
                           (nameCol == -1 ? "名称, " : "") +
                           (positionCol == -1 ? "岗位, " : "") +
                           (quantityCol == -1 ? "数量, " : "") +
                           (partTimeCol == -1 ? "兼职量, " : "") +
                           (wasteCol == -1 ? "损耗量" : ""));
                return false;
            }

            // 读取数据并计算工资
            Map<String, Double> processingGroupSalary = new HashMap<>();

            for (int i = headerRowIndex + 1; i <= cuttingSheet.getLastRowNum(); i++) {
                Row row = cuttingSheet.getRow(i);
                if (row == null) continue;

                // 获取名称、岗位、数量、兼职量
                Cell nameCell = row.getCell(nameCol);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                Cell positionCell = row.getCell(positionCol);
                if (positionCell == null) continue;

                String position = DataReader.getCellValueAsString(positionCell).trim();
                if (position.isEmpty()) continue;

                // 读取数量
                double quantity = 0;
                Cell quantityCell = row.getCell(quantityCol);
                if (quantityCell != null) {
                    quantity = DataReader.getCellValueAsDouble(quantityCell);
                }

                // 读取兼职量
                double partTime = 0;
                Cell partTimeCell = row.getCell(partTimeCol);
                if (partTimeCell != null) {
                    partTime = DataReader.getCellValueAsDouble(partTimeCell);
                }

                // 读取损耗量
                double wasteAmount = 0;
                Cell wasteCell = row.getCell(wasteCol);
                if (wasteCell != null) {
                    wasteAmount = DataReader.getCellValueAsDouble(wasteCell);
                }

                // 记录读取到的数据
                logger.info("第" + (i+1) + "行: 名称=" + name + ", 岗位=" + position +
                          ", 数量=" + quantity + ", 兼职量=" + partTime +
                          ", 损耗量=" + wasteAmount);

                // 根据岗位计算工资
                double salary = 0;
                // 计算损耗量的扣罚
                double wastePenalty = 0;
                // 允许的损耗量阈值 - 数量的千分之一
                double wasteThreshold = quantity > 0 ? quantity * 0.001 : 0;

                logger.info("员工 [" + name + "] 的损耗量阈值: " + wasteThreshold + " (数量 " + quantity + " × 0.001)");

                if (position.contains("机长")) {
                    // 基本工资计算
                    salary = quantity * 8 + partTime * 6;

                    // 计算损耗扣罚：如果损耗量超过阈值，机长每超出一单位扣600元
                    if (wasteAmount > wasteThreshold) {
                        wastePenalty = (wasteAmount - wasteThreshold) * 600;
                        logger.info("机长 [" + name + "] 损耗量 " + wasteAmount + " 超过阈值 " + wasteThreshold +
                                  "，扣罚: (" + wasteAmount + " - " + wasteThreshold + ") * 600 = " + wastePenalty);
                    }

                    // 最终工资 = 基本工资 - 损耗扣罚
                    salary = salary - wastePenalty;

                    // 保留两位小数
                    salary = Math.round(salary * 100) / 100.0;

                    if (wastePenalty > 0) {
                        logger.info("计算机长 [" + name + "] 工资: 数量(" + quantity + ")*8 + 兼职量(" + partTime + ")*6 - 损耗扣罚(" +
                                   wastePenalty + ") = " + salary);
                    } else {
                        logger.info("计算机长 [" + name + "] 工资: 数量(" + quantity + ")*8 + 兼职量(" + partTime + ")*6 = " + salary);
                    }
                } else if (position.contains("辅工")) {
                    // 基本工资计算
                    salary = quantity * 6 + partTime * 8;

                    // 计算损耗扣罚：如果损耗量超过阈值，辅工每超出一单位扣300元
                    if (wasteAmount > wasteThreshold) {
                        wastePenalty = (wasteAmount - wasteThreshold) * 300;
                        logger.info("辅工 [" + name + "] 损耗量 " + wasteAmount + " 超过阈值 " + wasteThreshold +
                                  "，扣罚: (" + wasteAmount + " - " + wasteThreshold + ") * 300 = " + wastePenalty);
                    }

                    // 最终工资 = 基本工资 - 损耗扣罚
                    salary = salary - wastePenalty;

                    // 保留两位小数
                    salary = Math.round(salary * 100) / 100.0;

                    if (wastePenalty > 0) {
                        logger.info("计算辅工 [" + name + "] 工资: 数量(" + quantity + ")*6 + 兼职量(" + partTime + ")*8 - 损耗扣罚(" +
                                   wastePenalty + ") = " + salary);
                    } else {
                        logger.info("计算辅工 [" + name + "] 工资: 数量(" + quantity + ")*6 + 兼职量(" + partTime + ")*8 = " + salary);
                    }
                } else {
                    logger.warn("未知岗位 [" + position + "] 员工: " + name + "，跳过工资计算");
                    continue;
                }

                // 保存工资计算结果
                if (salary > 0) {
                    // 如果同一人多条记录，累加工资
                    if (processingGroupSalary.containsKey(name)) {
                        salary += processingGroupSalary.get(name);
                    }
                    processingGroupSalary.put(name, salary);
                }
            }

            // 处理卡板工数据（如果需要）
            Sheet palletSheet = null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet curSheet = workbook.getSheetAt(i);
                String sheetName = curSheet.getSheetName();

                if (sheetName.contains("卡板工")) {
                    palletSheet = curSheet;
                    logger.info("找到卡板工工作表: " + sheetName);
                    break;
                }
            }

            if (palletSheet != null) {
                // 查找卡板工表头
                int palletNameCol = -1, palletBottomCol = -1, palletTopCol = -1;
                int palletHeaderRowIndex = -1;

                for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                    Row row = palletSheet.getRow(rowIndex);
                    if (row == null) continue;

                    palletNameCol = -1;
                    palletBottomCol = -1;
                    palletTopCol = -1;

                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        Cell cell = row.getCell(i);
                        if (cell == null) continue;

                        String value = DataReader.getCellValueAsString(cell).trim();
                        if (value.contains("名称")) palletNameCol = i;
                        else if (value.contains("底板数量")) palletBottomCol = i;
                        else if (value.contains("盖板数量")) palletTopCol = i;
                    }

                    if (palletNameCol != -1 && palletBottomCol != -1 && palletTopCol != -1) {
                        palletHeaderRowIndex = rowIndex;
                        logger.info("找到卡板工表头，在第" + (rowIndex+1) + "行");
                        break;
                    }
                }

                if (palletHeaderRowIndex != -1) {
                    // 读取卡板工数据并计算工资
                    for (int i = palletHeaderRowIndex + 1; i <= palletSheet.getLastRowNum(); i++) {
                        Row row = palletSheet.getRow(i);
                        if (row == null) continue;

                        Cell nameCell = row.getCell(palletNameCol);
                        if (nameCell == null) continue;

                        String name = DataReader.getCellValueAsString(nameCell).trim();
                        if (name.isEmpty()) continue;

                        // 读取底板数量和盖板数量
                        double bottomQuantity = 0;
                        Cell bottomCell = row.getCell(palletBottomCol);
                        if (bottomCell != null) {
                            bottomQuantity = DataReader.getCellValueAsDouble(bottomCell);
                        }

                        double topQuantity = 0;
                        Cell topCell = row.getCell(palletTopCol);
                        if (topCell != null) {
                            topQuantity = DataReader.getCellValueAsDouble(topCell);
                        }

                        // 卡板工工资计算：底板数量*2.8 + 盖板数量*1.2
                        double palletSalary = bottomQuantity * 2.8 + topQuantity * 1.2;
                        // 保留两位小数
                        palletSalary = Math.round(palletSalary * 100) / 100.0;
                        logger.info("计算卡板工 [" + name + "] 工资: 底板数量(" + bottomQuantity + ")*2.8 + 盖板数量(" + topQuantity + ")*1.2 = " + palletSalary);

                        // 保存卡板工工资
                        if (palletSalary > 0) {
                            // 如果同一人多条记录，累加工资
                            if (processingGroupSalary.containsKey(name)) {
                                palletSalary += processingGroupSalary.get(name);
                            }
                            processingGroupSalary.put(name, palletSalary);
                        }
                    }
                }
            }

            // 将计算结果写入输出表格
            if (!processingGroupSalary.isEmpty()) {
                int matchCount = 0;

                // 从第3行开始（索引为2），前两行是表头
                int startRowIndex = 2;

                // 遍历输出表格中的所有行
                for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 获取姓名列
                    Cell nameCell = row.getCell(nameColIndex);
                    if (nameCell == null) continue;

                    String name = DataReader.getCellValueAsString(nameCell).trim();
                    if (name.isEmpty()) continue;

                    // 查找对应的工资数据
                    Double salary = processingGroupSalary.get(name);
                    if (salary != null) {
                        // 查找现有的总奖金值
                        double existingBonus = 0;
                        Cell bonusCell = row.getCell(totalBonusColIndex);
                        if (bonusCell != null) {
                            existingBonus = DataReader.getCellValueAsDouble(bonusCell);
                        } else {
                            bonusCell = row.createCell(totalBonusColIndex);
                        }

                        // 使用数值格式写入
                        bonusCell.setCellType(CellType.NUMERIC);

                        // 如果已有奖金，则累加，否则直接设置
                        if (existingBonus > 0) {
                            double totalBonus = existingBonus + salary;
                            // 保留两位小数
                            totalBonus = Math.round(totalBonus * 100) / 100.0;
                            logger.info("员工 [" + name + "] 已有总奖金: " + existingBonus + "，加上加工组工资: " + salary + " = " + totalBonus);
                            bonusCell.setCellValue(totalBonus);
                    } else {
                            logger.info("设置员工 [" + name + "] 的总奖金为加工组工资: " + salary);
                            bonusCell.setCellValue(salary);
                        }

                        matchCount++;
                    }
                }

                logger.info("加工组工资数据写入完成，共匹配 " + matchCount + " 位员工");
            } else {
                logger.warn("没有计算出任何加工组工资数据");
            }

            return true;

        } catch (Exception e) {
            logger.error("处理加工组数据时出错: " + e.getMessage(), e);
            return false;
        }
    }




} 
